package models

import (
	"database/sql"
	"time"
)

type Book struct {
	ID         int64  `json:"id"`
	BookName   string `json:"book_name"`
	BookImg    string `json:"book_img"`
	BookAuthor string `json:"book_author"`
	IsDelete   int    `json:"is_delete"`
	CreatedAt  string `json:"created_at"`
	UpdatedAt  string `json:"updated_at"`
}

var db *sql.DB

// SetDB 设置数据库连接
func SetDB(database *sql.DB) {
	db = database
}

// GetBooks 获取所有未删除的图书，支持按书名搜索
func GetBooks(keyword string) ([]Book, error) {
	query := "SELECT id, book_name, book_img, book_author, is_delete, created_at, updated_at FROM book WHERE is_delete = 0"
	args := []interface{}{}

	if keyword != "" {
		query += " AND book_name LIKE ?"
		args = append(args, "%"+keyword+"%")
	}

	rows, err := db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var books []Book
	for rows.Next() {
		var book Book
		err := rows.Scan(&book.ID, &book.BookName, &book.BookImg, &book.BookAuthor, &book.IsDelete, &book.CreatedAt, &book.UpdatedAt)
		if err != nil {
			return nil, err
		}
		books = append(books, book)
	}

	return books, nil
}

// AddBook 添加新图书
func AddBook(book *Book, tagIDs []int64) error {
	// 开始事务
	tx, err := db.Begin()
	if err != nil {
		return err
	}

	// 设置时间戳和默认值
	now := time.Now().Format("2006-01-02 15:04:05")
	book.CreatedAt = now
	book.UpdatedAt = now
	book.IsDelete = 0

	// 插入图书数据
	result, err := tx.Exec(
		"INSERT INTO book (book_name, book_img, book_author, is_delete, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)",
		book.BookName, book.BookImg, book.BookAuthor, book.IsDelete, book.CreatedAt, book.UpdatedAt,
	)

	if err != nil {
		tx.Rollback()
		return err
	}

	// 获取插入的ID
	id, err := result.LastInsertId()
	if err != nil {
		tx.Rollback()
		return err
	}
	book.ID = id

	// 保存标签关系
	if len(tagIDs) > 0 {
		// 准备插入语句
		stmt, err := tx.Prepare("INSERT INTO book_tags_relation (book_id, tag_id, created_at) VALUES (?, ?, ?)")
		if err != nil {
			tx.Rollback()
			return err
		}
		defer stmt.Close()

		// 批量插入标签关系
		for _, tagID := range tagIDs {
			_, err = stmt.Exec(id, tagID, now)
			if err != nil {
				tx.Rollback()
				return err
			}
		}
	}

	// 提交事务
	return tx.Commit()
}

// GetBookByID 根据ID获取单本图书
func GetBookByID(id int64) (*Book, error) {
	var book Book
	row := db.QueryRow("SELECT id, book_name, book_img, book_author, is_delete, created_at, updated_at FROM book WHERE id = ? AND is_delete = 0", id)

	err := row.Scan(&book.ID, &book.BookName, &book.BookImg, &book.BookAuthor, &book.IsDelete, &book.CreatedAt, &book.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	return &book, nil
}

// UpdateBookWithTags 更新图书信息和标签关系
func UpdateBookWithTags(book *Book, tagIDs []int64) error {
	// 开始事务
	tx, err := db.Begin()
	if err != nil {
		return err
	}

	// 设置更新时间
	book.UpdatedAt = time.Now().Format("2006-01-02 15:04:05")

	// 更新图书信息
	_, err = tx.Exec(
		"UPDATE book SET book_name = ?, book_img = ?, book_author = ?, updated_at = ? WHERE id = ? AND is_delete = 0",
		book.BookName, book.BookImg, book.BookAuthor, book.UpdatedAt, book.ID,
	)
	if err != nil {
		tx.Rollback()
		return err
	}

	// 删除原有的标签关系
	_, err = tx.Exec("DELETE FROM book_tags_relation WHERE book_id = ?", book.ID)
	if err != nil {
		tx.Rollback()
		return err
	}

	// 添加新的标签关系
	if len(tagIDs) > 0 {
		// 准备插入语句
		stmt, err := tx.Prepare("INSERT INTO book_tags_relation (book_id, tag_id, created_at) VALUES (?, ?, ?)")
		if err != nil {
			tx.Rollback()
			return err
		}
		defer stmt.Close()

		// 批量插入标签关系
		for _, tagID := range tagIDs {
			_, err = stmt.Exec(book.ID, tagID, time.Now().Format("2006-01-02 15:04:05"))
			if err != nil {
				tx.Rollback()
				return err
			}
		}
	}

	// 提交事务
	return tx.Commit()
}

// GetBookTags 获取图书标签
func GetBookTags(bookID int64) ([]int64, error) {
	rows, err := db.Query("SELECT tag_id FROM book_tags_relation WHERE book_id = ?", bookID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tags []int64
	for rows.Next() {
		var tagID int64
		err := rows.Scan(&tagID)
		if err != nil {
			return nil, err
		}
		tags = append(tags, tagID)
	}

	return tags, nil
}

// SaveBookTags 保存图书标签关系
func SaveBookTags(bookID int64, tagIDs []int64) error {
	now := time.Now().Format("2006-01-02 15:04:05")

	// 开始事务
	tx, err := db.Begin()
	if err != nil {
		return err
	}

	// 准备插入语句
	stmt, err := tx.Prepare("INSERT INTO book_tags_relation (book_id, tag_id, created_at) VALUES (?, ?, ?)")
	if err != nil {
		tx.Rollback()
		return err
	}
	defer stmt.Close()

	// 批量插入标签关系
	for _, tagID := range tagIDs {
		_, err = stmt.Exec(bookID, tagID, now)
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	// 提交事务
	return tx.Commit()
}
